﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LibUtilities.dal;
using System.Data;
using System.Data.SqlClient;
using CLibArtreve.models;

namespace CLibArtreve.dal
{
    public class RoleDAL : SqlDatabase
    {
        #region CONSTRUCTORS
        public RoleDAL(string connectionString) : base(connectionString) { }
        #endregion

        #region OVERRIDE METHODS
        public override string ToString()
        {
            return "DATA ACCES LAYER: " + this.GetType().ToString();
        }
        #endregion

        #region GET METHODS
        public DataSet GetAll()
        {
            try
            {
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLROLES, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetById(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTROLEBYID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetByName(string label)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = label;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTROLEBYNAME, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetByUserId(Int64 userId)
        {
            try
            {
                string sQuery = @"
                    SELECT Roles.*
                    FROM UserRole
                    INNER JOIN Roles ON UserRole.role_id = Roles.role_id 
                    INNER JOIN Users ON UserRole.user_id = Users.user_id
                    WHERE Users.user_id = @user_id";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = userId;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(sQuery, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public DataSet GetByUserName(string userName)
        {
            try
            {
                string sQuery = @"
                    SELECT Roles.*
                    FROM UserRole
                    INNER JOIN Roles ON UserRole.role_id = Roles.role_id 
                    INNER JOIN Users ON UserRole.user_id = Users.user_id
                    WHERE Users.user_email = @user_name";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_name";
                parameter.Value = userName;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(sQuery, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region INSERT METHODS
        public int Insert(string label)
        {
            try
            {
                // MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = label;
                parameters.Add(parameter);

                // EXECUTE
                int p_id = ExecuteNonQueryWithCommonQuery(Queries.INSERTROLE, parameters, true);
                if (p_id > 0)
                {
                    p_id = Convert.ToInt32(ExecuteScalarWithCommonQuery<decimal>(Queries.SELECTIDENTITYAFTERINSERTSQLSERVER));
                }
                return p_id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertUserInRole(string userName, string roleName)
        {
            try
            {
                //GET USER
                UserDAL userDal = new UserDAL(ConnectionString);
                Int64 user_id = Convert.ToInt64(userDal.GetUserByEmail(userName).Tables[0].Rows[0]["user_id"]);

                //GET ROLE
                Int64 role_id = Convert.ToInt64(GetByName(roleName).Tables[0].Rows[0]["role_id"]);

                string sQuery = @"
                    INSERT 
                    INTO UserRoles
                    (user_id, role_id)
                    VALUES
                    (@user_id, @role_id)";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = user_id;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "role_id";
                parameter.Value = role_id;
                parameters.Add(parameter);

                return ExecuteNonQueryWithCommonQuery(sQuery, parameters, true);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region UPDATE METHODS
        public int Update(Int64 id, string label)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "id";
                parameter.Value = id;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = label;
                parameters.Add(parameter);

                return ExecuteNonQueryWithCommonQuery(Queries.UPDATEROLE, parameters, true);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region DELETE METHODS
        public int DeleteById(Int64 id)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                //MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "id";
                parameter.Value = id;
                parameters.Add(parameter);

                //EXECUTE
                return ExecuteNonQueryWithCommonQuery(Queries.DELETEROLE, parameters, true);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public int DeleteByName(string label)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                //MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = label;
                parameters.Add(parameter);

                //EXECUTE
                return ExecuteNonQueryWithCommonQuery(Queries.DELETEROLEBYNAME, parameters, true);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public int DeleteUserFromRole(string userName, string name)
        {
            try
            {
                string sQuery = @"
                    DELETE UR
                    FROM UserRole AS UR
                    INNER JOIN Roles ON UR.role_id = Roles.role_id 
                    INNER JOIN Users ON UR.user_id = Users.user_id
                    WHERE Roles.role_label = @label
                    AND Users.user_email = @user_name";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = name;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_name";
                parameter.Value = userName;
                parameters.Add(parameter);

                return ExecuteNonQueryWithCommonQuery(sQuery, parameters, false);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region UTILITIES
        public bool RoleExists(string name)
        {
            try
            {
                string sQuery = @"
                    SELECT COUNT(role_id)
                    FROM Roles
                    WHERE role_label = @label";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = name;
                parameters.Add(parameter);

                int count = (int)ExecuteScalarWithCommonQuery<Int32>(sQuery, parameters);
                if (count == 0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public bool IsUserInRole(string userName, string name)
        {
            try
            {
                string sQuery = @"
                    SELECT COUNT(user_id)
                    FROM UserRole
                    INNER JOIN Roles ON UserRole.role_id = Roles.role_id 
                    INNER JOIN Users ON UserRole.user_id = Users.user_id
                    WHERE Roles.role_label = @label
                    AND Users.user_email = @user_name";

                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "label";
                parameter.Value = name;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_name";
                parameter.Value = userName;
                parameters.Add(parameter);

                int count = (int)ExecuteScalarWithCommonQuery<Int32>(sQuery, parameters);
                if (count == 0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion
    }
}
